CREATE OR REPLACE
procedure full_backup_transable_variant(source_table_name IN varchar2, --源表基表名称
                                             dest_table_name   in varchar2, --目标表基表名称
                                             transable_param   in varchar2, --本地网、客户分组、清单序列、进程号等，使用逗号将多个本地网隔开，格式如:"本地网1,本地网2,本地网3...",
                                             ret               out varchar2)  AUTHID CURRENT_USER is
  v_source_table varchar2(100);
  v_dest_table   varchar2(100);
  i              number(9);
  j              number(3);
  k              number(3);
  v_count        number(12);
  v_tcount       number(2); --表个数，用以判断表是否存在
  v_sql          varchar2(500);
  v_ret          varchar2(1000);
 log_output      varchar2(1000);
  --字符分割使用
  v_str       varchar2(100);
  v_strs_last varchar2(100) := transable_param;
begin
  v_ret := '"result":{';
  log_output := '"log":{';

  if v_strs_last = '-9' then

    v_source_table := source_table_name;
    v_dest_table   := dest_table_name;
    v_sql          := 'select count(*) as total from ' || v_source_table;
    log_output := log_output || '"执行记录数查询语句":"' || v_sql;
    EXECUTE IMMEDIATE v_sql
      into v_count;
    log_output := log_output || '","记录数":"' || v_count;

    --判断目标表是否存在，存在则先将其改名
    v_sql := 'select count(*) from user_tables where table_name=UPPER(''' ||
             v_dest_table || ''')';
    log_output := log_output || '","判断目标表是否存在":"' || v_sql;
    EXECUTE IMMEDIATE v_sql
      into v_tcount;
    if v_tcount = 1 then
      v_sql := 'rename ' || v_dest_table || ' to ' || v_dest_table || '_bak' || to_char(sysdate,'ddhh24miss');
      log_output := log_output || '","已存在目标表改名":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
    end if;

    v_sql := 'rename ' || v_source_table || ' to ' || v_dest_table;
    log_output := log_output || '","执行rename语句":"' || v_sql;
    EXECUTE IMMEDIATE v_sql;
      v_sql := 'create table ' || v_source_table || ' as select * from ' || v_dest_table || ' where 1=0';
      log_output := log_output || '","重建表结构":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;

    v_ret := v_ret || '"' || v_source_table || '":"' || v_count || '"';

DECLARE
   constraintsname varchar(100);--约束名
   columnname varchar(100);--字段名
   columnnameR varchar(100);--外键关联的字段
   tablenameR varchar(100);--外键关联的表名
   constraintstype varchar(100);--
   ctype varchar(100);   --约束类型 key：主外键  index：索引
   pkey char(30);
   TYPE c1 IS REF CURSOR;
   temp_cursor c1;




begin

 --主键
	ctype := 'key';
	OPEN temp_cursor
		FOR
			SELECT user_cons_columns.constraint_name,column_name,constraint_type
			FROM USER_CONSTRAINTS LEFT JOIN  user_cons_columns ON user_cons_columns.constraint_name = USER_CONSTRAINTS.constraint_name
			WHERE USER_CONSTRAINTS.TABLE_name = UPPER(dest_table_name) AND constraint_type='P';
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
        log_output := log_output || '","遍历主键，并删除目标表主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' primary key('||columnname||')';
        log_output := log_output || '","为源表加上主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
	pkey :=  columnname;
    END loop;



	--外键
	OPEN temp_cursor
		FOR
			select
			       t2.table_name,
			       t1.constraint_name,
			       a1.column_name,
			       a2.column_name as "COLUMN_NAME(R)",
			       t1.constraint_type
			from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
			where
				t1.r_constraint_name = t2.constraint_name and
				t1.constraint_name = a1.constraint_name and
				t1.r_constraint_name = a2.constraint_name  AND
				t1.TABLE_NAME = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO tablenameR,constraintsname, columnname,columnnameR,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
	log_output := log_output || '","遍历外键，并删除目标表外键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' foreign key('||columnname||') references '||tablenameR||'('||columnnameR||')';
        log_output := log_output || '","为源表加上外键":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
        END loop;
	COMMIT;



	--索引
	ctype := 'index';
	OPEN temp_cursor
		FOR
			SELECT user_indexes.index_name,column_name,user_indexes.uniqueness
			FROM user_indexes LEFT JOIN  user_ind_columns ON user_indexes.index_name = user_ind_columns.index_name
			WHERE user_indexes.TABLE_name = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'drop INDEX ' || constraintsname;
	log_output := log_output || '","遍历索引，并删除目标表索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;



	IF constraintstype = 'UNIQUE' then --判断是否唯一索引
    	    IF columnname <> pkey THEN  --主键会自动创建唯一索引，所以不需要重新创建
    	  		 v_sql := 'create index UNIQUE '||constraintsname||' on '||source_table_name||'('||columnname||')';
    		END IF;
	else
             	         v_sql := 'create index '||constraintsname||' on '||source_table_name||'('||columnname||')';
        end if;
        log_output := log_output || '","为源表加上索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;

        END loop;
	COMMIT;
CLOSE temp_cursor;

end;




  else
    --计算逗号个数
    v_sql := 'select nvl(lengthb(regexp_replace(''' || transable_param ||
             ''',''[^,]'',null)),0)  as res from dual';
    EXECUTE IMMEDIATE v_sql
      into j;
    k := j + 1;
    --传入本地网分割
    loop
      i := instr(v_strs_last, ',');
      exit when k = 0;
      if k = 1 then
        v_str       := v_strs_last;
        v_strs_last := substr(v_strs_last, i + 1);
      else
        v_str       := substr(v_strs_last, 1, i - 1);
        v_strs_last := substr(v_strs_last, i + 1);
      end if;
      log_output := log_output || '"参数":"' || v_str;

      v_source_table := source_table_name || '_' || v_str;
      v_dest_table   := dest_table_name || '_' || v_str;
      --取源表数据量
      v_sql := 'select count(*) as total from ' || v_source_table;
      log_output := log_output || '","执行记录数查询语句":"' || v_sql;
      EXECUTE IMMEDIATE v_sql
        into v_count;
      log_output := log_output || '","记录数":"' || v_count;

      --判断目标表是否存在，存在则先drop
      v_sql := 'select count(*) from user_tables where table_name=UPPER(''' ||
               v_dest_table || ''')';
      log_output := log_output || '","判断目标表是否存在":"' || v_sql;
      EXECUTE IMMEDIATE v_sql
        into v_tcount;
      if v_tcount = 1 then
        v_sql := 'rename table ' || v_dest_table || ' to ' || v_dest_table || '_bak' || to_char(sysdate,'ddhh24miss');
        log_output := log_output || '","已存在目标表改名":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
      end if;

      --rename
      v_sql := 'rename ' || v_source_table || ' to ' || v_dest_table;
      log_output := log_output || '","执行rename语句":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
      v_sql := 'create table' || v_source_table || ' as select * from ' || v_dest_table || ' where 1=0';
      log_output := log_output || '","重建表结构":"' || v_sql;
      EXECUTE IMMEDIATE v_sql;
      --记录源表记录数
      if k = j then
        v_ret := v_ret || '"' || v_source_table || '":"' || v_count || '"';
      else
        v_ret := v_ret || ',""' || v_source_table || '":"' || v_count || '"';
DECLARE
   constraintsname varchar(100);--约束名
   columnname varchar(100);--字段名
   columnnameR varchar(100);--外键关联的字段
   tablenameR varchar(100);--外键关联的表名
   constraintstype varchar(100);--
   ctype varchar(100);   --约束类型 key：主外键  index：索引
   pkey char(30);
   TYPE c1 IS REF CURSOR;
   temp_cursor c1;




begin

 --主键
	ctype := 'key';
	OPEN temp_cursor
		FOR
			SELECT user_cons_columns.constraint_name,column_name,constraint_type
			FROM USER_CONSTRAINTS LEFT JOIN  user_cons_columns ON user_cons_columns.constraint_name = USER_CONSTRAINTS.constraint_name
			WHERE USER_CONSTRAINTS.TABLE_name = UPPER(dest_table_name) AND constraint_type='P';
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
        log_output := log_output || '","遍历主键，并删除目标表主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' primary key('||columnname||')';
        log_output := log_output || '","为源表加上主键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
	pkey :=  columnname;
    END loop;



	--外键
	OPEN temp_cursor
		FOR
			select
			       t2.table_name,
			       t1.constraint_name,
			       a1.column_name,
			       a2.column_name as "COLUMN_NAME(R)",
			       t1.constraint_type
			from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
			where
				t1.r_constraint_name = t2.constraint_name and
				t1.constraint_name = a1.constraint_name and
				t1.r_constraint_name = a2.constraint_name  AND
				t1.TABLE_NAME = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO tablenameR,constraintsname, columnname,columnnameR,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'ALTER TABLE '|| dest_table_name || ' DROP CONSTRAINT ' || constraintsname;
	log_output := log_output || '","遍历外键，并删除目标表外键":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;
    	v_sql := 'alter table '|| source_table_name ||' add constraint '||constraintsname||' foreign key('||columnname||') references '||tablenameR||'('||columnnameR||')';
        log_output := log_output || '","为源表加上外键":"' || v_sql;
        EXECUTE IMMEDIATE v_sql;
        END loop;
	COMMIT;



	--索引
	ctype := 'index';
	OPEN temp_cursor
		FOR
			SELECT user_indexes.index_name,column_name,user_indexes.uniqueness
			FROM user_indexes LEFT JOIN  user_ind_columns ON user_indexes.index_name = user_ind_columns.index_name
			WHERE user_indexes.TABLE_name = UPPER(dest_table_name);
	LOOP
         FETCH temp_cursor INTO constraintsname, columnname,constraintstype;--获得当前记录的数据
    EXIT WHEN temp_cursor%NOTFOUND;
    	v_sql := 'drop INDEX ' || constraintsname;
	log_output := log_output || '","遍历索引，并删除目标表索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;



	IF constraintstype = 'UNIQUE' then --判断是否唯一索引
    	    IF columnname <> pkey THEN  --主键会自动创建唯一索引，所以不需要重新创建
    	  		 v_sql := 'create index UNIQUE '||constraintsname||' on '||source_table_name||'('||columnname||')';
    		END IF;
	else
             	         v_sql := 'create index '||constraintsname||' on '||source_table_name||'('||columnname||')';
        end if;
        log_output := log_output || '","为源表加上索引":"' || v_sql;
	EXECUTE IMMEDIATE v_sql;

        END loop;
	COMMIT;
CLOSE temp_cursor;

end;






      end if;

      k := k - 1;
    end loop;
  end if;

  v_ret := '{' || v_ret || '},' || log_output || '"}}';
  ret := v_ret;

  return;

EXCEPTION
  WHEN OTHERS THEN
    ret := '{' || log_output || '"},"error":"' || sqlcode || sqlerrm || '"}';

end full_backup_transable_variant;

